package com.wangningbo.dao;

import com.wangningbo.pojo.LinkMan;
import com.wangningbo.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author wangningbo
 * @Date 2021/1/10 12:47
 */

public class LinkManDao {
    private QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
    public List<LinkMan> findAll() throws SQLException {
        String sql = "select * from linkman";
        List<LinkMan> linkMans = queryRunner.query(sql, new BeanListHandler<LinkMan>(LinkMan.class));
        return linkMans;
    }

    public void addLinkMan(LinkMan linkMan) throws SQLException {
        String sql = " insert into linkman values (null,?,?,?,?,?,?)";
        queryRunner.update(sql,linkMan.getName(),
                                linkMan.getSex(),
                                linkMan.getAge(),
                                linkMan.getAddress(),
                linkMan.getQq(),
                linkMan.getEmail());
    }

    public void deleteLinkManById(Integer id) throws SQLException {
        String sql = "delete from linkman where id = ?";
        queryRunner.update(sql,id);
    }

    public LinkMan findOne(Integer id) throws SQLException {
        String sql = "select * from linkman where id=?";
        LinkMan linkMan = queryRunner.query(sql, new BeanHandler<LinkMan>(LinkMan.class), id);
        return linkMan;
    }

    public void updateLinkMan(LinkMan linkMan,Integer id) throws SQLException {
        String sql = "update linkman set name =?,sex=?,age=?,address=?,qq=?,email=? where id = ?";
        queryRunner.update(sql,linkMan.getName(),
                linkMan.getSex(),
                linkMan.getAge(),
                linkMan.getAddress(),
                linkMan.getQq(),
                linkMan.getEmail(),id);
    }

    public Long findTotalSize() throws SQLException {
        String sql = "select count(*) from linkman";
        Long totalSize = (Long) queryRunner.query(sql,new ScalarHandler());
        return totalSize;
    }

    public List<LinkMan> findByPage(Long currentPage, Integer pageSize) throws SQLException {
        String sql = "select * from linkman  limit ?,?";
        List<LinkMan> linkManList = queryRunner.query(sql, new BeanListHandler<LinkMan>(LinkMan.class), (currentPage - 1) * pageSize, pageSize);
        return linkManList;
    }
}
